<?php

class InvoicesDAO{
    
    public function InvoicesDAO(){
    
    }
    
            /*  invoices
            id
            date
            clientsId
            status (ac, ca)
            
            invoices_products
            invoicesId
            productsId
            quantity
            price
        */

	/**
     * @param Invoice $invoice
     * @return true if the excecution was successful, false if opposite
     */
    public function addInvoice($invoice){
        $bd = new Bd();
        
        $sql = "INSERT INTO invoices (date, clientsId, status) 
        		VALUES ('".$invoice->getDate()."', ".$invoice->getClient()->getId().", 'ac');";
        if($bd->doUpdate($sql)){
            $sql = "SELECT MAX(id) FROM invoices WHERE date = '".$invoice->getDate()."', clientsId = ".$invoice->getClient()->getId().", status = 'ac'";
            $id = $bd->doQueryOne($sql);
            if(isset($id) && $this->howManyInvoiceProducts($id) == 0){
                $prods = $invoice->getProducts();
                for($i=0; $i < sizeof($prods); $i++){
                    $sql = "INSERT INTO invoices_products (invoicesId, productsId, quantity, price)
                            VALUES  ".$id.", ".$prods->getProduct()->getId().", ".$prods->getQuantity().", ".$prods->getPrice().";";
                    return $bd->doUpdate($sql);
                }
            }
            else{
                //error
                return false;
            }
        }
        else{
            //error
            return false;
        }
        
    }
    
    /**
     * @param int $idInvoice > 0
     * @return array if the invoice exists, false otherwise
     */
    public function getInvoice($idInvoice){
        if($this->existInvoice($idInvoice)){
            $bd = new Bd();        
            $sql = "SELECT * FROM invoices WHERE id = ".$idInvoice.";";            
            return $bd->doQueryRow($sql);
        }
        return false;
    }
    
    /**
     * @param int $idInvoice > 0
     * @return true if the invoice exists, false otherwise
     */
    public function existInvoice($idInvoice){
        $bd = new Bd();
        
        $sql = "SELECT COUNT(*) FROM invoices WHERE id = ".$idInvoice.";";
        
        if ($bd->doQueryOne($sql) > 0)
            return true;
        else return false;
    }
    
    /**
     * @param int $idInvoice > 0
     * @return true if the invoice could be cancelled, false otherwise or if it was already cancelled
     */
    public function cancelInvoice($idInvoice){
        if($this->isInvoicetActive($idInvoice)){
            $bd = new Bd();        
            $sql = "UPDATE invoices SET status = 'ca' WHERE id = ".$idInvoice.";";            
            return $bd->doUpdate($sql);
        }
        return false;
    }

    /**
     * @param int $idInvoice > 0
     * @return true if the invoice is active, false otherwise or if it doesn't exist
     */
    public function isInvoiceActive($idInvoice){
        if($this->existInvoice($idInvoice)){
            $bd = new Bd();        
            $sql = "SELECT COUNT(*) FROM invoices WHERE id = ".$idInvoice." and status = 'ac';";            
            if ($bd->doQueryOne($sql) > 0)
                return true;
            else return false;
        }
        return false;
    }
    
    /**
     * @return array with all existing invoices
     */
    public function getInvoices(){
        $bd = new Bd();        
        $sql = "SELECT * FROM invoices;";            
        return $bd->doQueryAll($sql);
    }
    
    /**
     * @return array with all existing invoices
     */
    public function searchInvoice($id){
        $bd = new Bd();        
        $sql = "SELECT * FROM invoices WHERE id = ".$id." ;";
        return $bd->doQueryAll($sql);
    }
    
    /**
     * @param string $code valid code
     * @return true if found false otherwise
     */
    public function existInvoiceCode($code){
        $bd = new Bd();        
        $sql = "SELECT COUNT(*) FROM invoices WHERE code = '".$code."' ;";
        if ($bd->doQueryOne($sql) > 0)
            return true;
        else return false;
    }
    
    
    /**
     * @param string $id valid id
     * @return true if found false otherwise
     */
    public function existInvoiceReference($ref){
        $bd = new Bd();        
        $sql = "SELECT COUNT(*) FROM invoices WHERE reference = '".$ref."' ;";
        if ($bd->doQueryOne($sql) > 0)
            return true;
        else return false;
    }    
    
    /**
     * @param int $idClient > 0
     * @return true if the client exists, false otherwise
     */
    public function existClient($idClient){
        $bd = new Bd();
        
        $sql = "SELECT COUNT(*) FROM clients WHERE id = ".$idClient.";";
        
        if ($bd->doQueryOne($sql) > 0)
            return true;
        else return false;
    }
        
    /**
     * @param int $aId > 0
     * @return array with all matching products
     */
    public function getInvoiceProducts($aId){
        $bd = new Bd();        
        $sql = "SELECT I.quantity, I.price AS totalPrice, P.code, P.reference, P.name, P.description, P.presentation, P.picture, P.stock, P.minStock, P.price FROM invoices_products I, products P WHERE I.invoicesId = ".$aId." and I.productsId = P.id ;";
        return $bd->doQueryAll($sql);
    }
    
    /**
     * @param int $aId > 0
     * @return int with the quantity of matching products
     */
    public function howManyInvoiceProducts($aId){
        $bd = new Bd();        
        $sql = "SELECT COUNT(*) FROM invoices_products I, products P WHERE I.invoicesId = ".$aId." and I.productsId = P.id ;";
        return $bd->doQueryOne($sql);
    }
    
    /**
     * @param int $aId > 0
     * @return array with matching client
     */
    public function getInvoiceClient($aId){
        $bd = new Bd();        
        $sql = "SELECT C.idType, C.clientId, C.name, C.lastName, C.address, C.phone, C.email, C.access FROM invoices I, clients C WHERE I.id = ".$aId." and I.clientsId = C.id ;";
        return $bd->doQueryRow($sql);
    }
}

?>